using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Configuration;
using System.Data;
using System.Text;
using System.Globalization;

using MySql.Data.MySqlClient;
using MySql.Data.Types;

namespace BlogEngine.Core.Providers
{
	/// <summary>
	/// 
	/// </summary>
	public class MySqlBlogProvider : BlogProvider
	{
		#region Generic IPostable

		/// <summary>
		/// Reads the postable.
		/// </summary>
		/// <param name="reader">The reader.</param>
		/// <returns></returns>
		private IPostable ReadPostable(MySqlDataReader reader)
		{
			IPostable post;

			string type = reader.GetString("type");

			switch (type)
			{
				case "post": post = new Post(); break;
				case "page": post = new Page(); break;
				case "attachment": post = new Attachment(); break;
				default: goto case "post";
			}

			post.BeginInit();
			post.Id = reader.GetInt32("post_id");
			post.Author = reader.GetString("user_name");

			if (!reader.IsDBNull(reader.GetOrdinal("parent_id")))
				post.Parent = SelectPostable(reader.GetInt32("parent_id"));

			post.Type = (PostType)Enum.Parse(typeof(PostType), reader.GetString("type"), true);
			post.Status = (PostStatusType)Enum.Parse(typeof(PostStatusType), reader.GetString("status"), true);
			post.CommentStatus = (PostCommentStatusType)Enum.Parse(typeof(PostCommentStatusType), reader.GetString("comment_status"), true);
			post.PingStatus = (PostPingStatusType)Enum.Parse(typeof(PostPingStatusType), reader.GetString("ping_status"), true);

			if (!reader.IsDBNull(reader.GetOrdinal("mime_type")))
				post.MimeType = reader.GetString("mime_type");

			post.Slug = reader.GetString("slug");
			post.PermaLink = new Uri(reader.GetString("permalink"));
			post.DateCreated = reader.GetDateTime("creation_date");
			post.DatePublished = reader.GetDateTime("publish_date");
			post.DateModified = reader.GetDateTime("modified_date");
			post.Title = reader.GetString("title");
			post.Excerpt = reader.GetString("excerpt");
			post.Content = reader.GetString("content");
			post.EndInit();

			return post;
		}

		/// <summary>
		/// Selects the single post.
		/// </summary>
		/// <param name="sql">The SQL.</param>
		/// <param name="parameters">The parameters.</param>
		/// <returns></returns>
		private IPostable SelectSinglePost(string sql, IDictionary<string, object> parameters)
		{
			using (MySqlConnection connection = new MySqlConnection(BlogSettings.Instance.ConnectionString))
			{
				using (MySqlCommand command = connection.CreateCommand())
				{
					command.CommandText = sql;
					command.CommandType = CommandType.Text;

					foreach (string key in parameters.Keys)
						command.Parameters.AddWithValue(key, parameters[key]);

					return SelectSinglePost(command);
				}
			}
		}

		/// <summary>
		/// Selects the single post.
		/// </summary>
		/// <param name="command">The command.</param>
		/// <returns></returns>
		private IPostable SelectSinglePost(MySqlCommand command)
		{
			IPostable post = null;

			if (command.Connection.State != ConnectionState.Open)
				command.Connection.Open();

			using (MySqlDataReader reader = command.ExecuteReader())
			{
				if (reader.Read())
					post = ReadPostable(reader);
			}

			if (post != null)
				post.FillTerms(SelectTermsByPost(post.Id, command.Connection));

			command.Connection.Close();

			return post;
		}

		/// <summary>
		/// Selects the multiple posts.
		/// </summary>
		/// <param name="sql">The SQL.</param>
		/// <param name="parameters">The parameters.</param>
		/// <returns></returns>
		private List<IPostable> SelectMultiplePosts(string sql, IDictionary<string, object> parameters)
		{
			using (MySqlConnection connection = new MySqlConnection(BlogSettings.Instance.ConnectionString))
			{
				using (MySqlCommand command = connection.CreateCommand())
				{
					command.CommandText = sql;
					command.CommandType = CommandType.Text;

					foreach (string key in parameters.Keys)
						command.Parameters.AddWithValue(key, parameters[key]);

					return SelectMultiplePosts(command);
				}
			}
		}

		/// <summary>
		/// Selects the multiple posts.
		/// </summary>
		/// <param name="command">The command.</param>
		/// <returns></returns>
		private List<IPostable> SelectMultiplePosts(MySqlCommand command)
		{
			List<IPostable> list = new List<IPostable>();

			if (command.Connection.State != ConnectionState.Open)
				command.Connection.Open();

			using (MySqlDataReader reader = command.ExecuteReader())
			{
				while (reader.Read())
				{
					IPostable post = ReadPostable(reader);
					list.Add(post);
				}
			}

			foreach(IPostable post in list)
				post.FillTerms(SelectTermsByPost(post.Id, command.Connection));

			command.Connection.Close();

			return list;
		}

		/// <summary>
		/// Retrieves a post based on the specified Id.
		/// </summary>
		/// <param name="id">The id.</param>
		/// <returns></returns>
		private IPostable SelectPostable(int id)
		{
			Dictionary<string, object> parameters = new Dictionary<string, object>();
			parameters.Add("?id", id);

			return SelectSinglePost(@"select * from blog_posts where post_id = ?id", parameters);
		}

		/// <summary>
		/// Retrieves a post based on the specified Id.
		/// </summary>
		/// <param name="id">The id.</param>
		/// <returns></returns>
		private IPostable SelectNextPostable(int id)
		{
			Dictionary<string, object> parameters = new Dictionary<string, object>();
			parameters.Add("?id", id);

			return SelectSinglePost(@"select * from blog_posts where post_id = ?id", parameters);
		}

		/// <summary>
		/// Retrieves a post based on the specified Id.
		/// </summary>
		/// <param name="id">The id.</param>
		/// <returns></returns>
		private IPostable SelectPreviousPostable(int id)
		{
			Dictionary<string, object> parameters = new Dictionary<string, object>();
			parameters.Add("?id", id);

			return SelectSinglePost(@"select * from blog_posts where post_id = ?id", parameters);
		}

		/// <summary>
		/// Retrieves a Post from the provider based on the specified id.
		/// </summary>
		/// <param name="permalink">The permalink.</param>
		/// <returns></returns>
		private IPostable SelectPostable(string permalink)
		{
			Dictionary<string, object> parameters = new Dictionary<string, object>();
			parameters.Add("?permalink", permalink);

			return SelectSinglePost(@"select * from blog_posts where permalink = ?permalink", parameters);
		}

		/// <summary>
		/// Selects the postable by author.
		/// </summary>
		/// <param name="author">The author.</param>
		/// <returns></returns>
		private List<IPostable> SelectPostableByAuthor(string author, int startIndex, int count)
		{
			Dictionary<string, object> parameters = new Dictionary<string, object>();
			parameters.Add("?author", author);
			parameters.Add("?start", startIndex);
			parameters.Add("?count", count);

			return SelectMultiplePosts(@"select * from blog_posts where user_name = ?author order by publish_date desc limit ?start, ?count", parameters);
		}

		/// <summary>
		/// Selects the postable by date.
		/// </summary>
		/// <param name="dateFrom">The date from.</param>
		/// <param name="dateTo">The date to.</param>
		/// <returns></returns>
		private List<IPostable> SelectPostableByDate(DateTime dateFrom, DateTime dateTo, int startIndex, int count)
		{
			Dictionary<string, object> parameters = new Dictionary<string, object>();
			parameters.Add("?dateFrom", dateFrom < dateTo ? dateFrom : dateTo);
			parameters.Add("?dateTo", dateTo > dateFrom ? dateTo : dateFrom);
			parameters.Add("?start", startIndex);
			parameters.Add("?count", count);

			return SelectMultiplePosts(@"select * from blog_posts where publish_date between ?dateFrom and ?dateTo order by publish_date desc limit ?start, ?count", parameters);
		}

		/// <summary>
		/// Selects the postable by parent post.
		/// </summary>
		/// <param name="postId">The post id.</param>
		/// <returns></returns>
		private List<IPostable> SelectPostableByParentPost(int postId, int startIndex, int count)
		{
			Dictionary<string, object> parameters = new Dictionary<string, object>();
			parameters.Add("?parent", postId);
			parameters.Add("?start", startIndex);
			parameters.Add("?count", count);

			return SelectMultiplePosts(@"select * from blog_posts where parent_id = ?parent order by publish_date desc limit ?start, ?count", parameters);
		}

		/// <summary>
		/// Selects the postable by term.
		/// </summary>
		/// <param name="postType">Type of the post.</param>
		/// <param name="termType">Type of the term.</param>
		/// <param name="slug">The slug.</param>
		/// <param name="startIndex">The start index.</param>
		/// <param name="count">The count.</param>
		/// <returns></returns>
		private List<IPostable> SelectPostableByTerm(PostType postType, TermType termType, string slug, int startIndex, int count)
		{
			Dictionary<string, object> parameters = new Dictionary<string, object>();
			parameters.Add("?postType", postType);
			parameters.Add("?termType", termType);
			parameters.Add("?slug", slug);
			parameters.Add("?start", startIndex);
			parameters.Add("?count", count);

			string command = @"
select * 
from 
	blog_posts p 
	inner join blog_term_link l on p.post_id = l.post_id 
	inner joing blog_terms t on t.term_id = l.term_id 
where 
	p.type = ?postType 
	and t.type = ?termType
	and t.slug = ?slug
order by 
	publish_date desc 
limit 
	?start, ?count";

			return SelectMultiplePosts(command, parameters);
		}

		/// <summary>
		/// Selects the postable.
		/// </summary>
		/// <param name="type">The type.</param>
		/// <param name="startIndex">The start index.</param>
		/// <param name="count">The count.</param>
		/// <returns></returns>
		private List<IPostable> SelectPostable(PostType type, int startIndex, int count)
		{
			Dictionary<string, object> parameters = new Dictionary<string, object>();
			parameters.Add("?type", type.ToString());
			parameters.Add("?start", startIndex);
			parameters.Add("?count", count);

			return SelectMultiplePosts(@"select * from blog_posts where type = ?type order by publish_date desc limit ?start, ?count", parameters);
		}

		/// <summary>
		/// Counts the postable.
		/// </summary>
		/// <param name="type">The type.</param>
		/// <returns></returns>
		private long CountPostable(PostType type)
		{
			using (MySqlConnection connection = new MySqlConnection(BlogSettings.Instance.ConnectionString))
			{
				using (MySqlCommand command = connection.CreateCommand())
				{
					command.CommandText = @"select count(*) from blog_posts where type = ?type";
					command.CommandType = CommandType.Text;

					command.Parameters.AddWithValue("?type", type.ToString());

					connection.Open();
					long count = (long)command.ExecuteScalar();
					connection.Close();

					return count;
				}
			} 
		}

		/// <summary>
		/// Inserts a new Post to the data store.
		/// </summary>
		/// <param name="post">The post.</param>
		private void InsertPostable(IPostable post)
		{
			UpdatePostable(post);
		}

		/// <summary>
		/// Updates a Post.
		/// </summary>
		/// <param name="post">The post.</param>
		private void UpdatePostable(IPostable post)
		{
			using (MySqlConnection connection = new MySqlConnection(BlogSettings.Instance.ConnectionString))
			{
				using (MySqlCommand command = connection.CreateCommand())
				{
					command.CommandType = CommandType.Text;
					command.CommandText = @"
insert into blog_posts (
	user_name, 
	parent_id, 
	type, 
	status, 
	comment_status, 
	ping_status, 
	mime_type, 
	slug, 
	permalink, 
	creation_date,
	publish_date,
	title,
	excerpt,
	content
) values (
	?user_name, 
	?parent_id, 
	?type, 
	?status, 
	?comment_status, 
	?ping_status, 
	?mime_type, 
	?slug, 
	?permalink, 
	now(),
	?publish_date,
	?title,
	?excerpt,
	?content
) on duplicate key update
	post_id = ?post_id, 
	user_name = ?user_name, 
	parent_id = ?parent_id, 
	type = ?type, 
	status = ?status, 
	comment_status = ?comment_status, 
	ping_status = ?ping_status, 
	mime_type = ?mime_type, 
	slug = ?slug, 
	permalink = ?permalink,
	publish_date = ?publish_date,
	title = ?title,
	excerpt = ?excerpt,
	content = ?content;";

					command.Parameters.AddWithValue("?post_id", post.Id);
					command.Parameters.AddWithValue("?user_name", post.Author);
					command.Parameters.AddWithValue("?parent_id", (post.Parent == null) ? (int?)null : post.Parent.Id);
					command.Parameters.AddWithValue("?type", post.Type.ToString());
					command.Parameters.AddWithValue("?status", post.Status.ToString());
					command.Parameters.AddWithValue("?comment_status", post.CommentStatus.ToString());
					command.Parameters.AddWithValue("?ping_status", post.PingStatus.ToString());
					command.Parameters.AddWithValue("?mime_type", post.MimeType);
					command.Parameters.AddWithValue("?slug", post.Slug);
					command.Parameters.AddWithValue("?permalink", post.PermaLink);
					command.Parameters.AddWithValue("?publish_date", post.DatePublished);
					command.Parameters.AddWithValue("?title", post.Title);
					command.Parameters.AddWithValue("?excerpt", post.Excerpt);
					command.Parameters.AddWithValue("?content", post.Content);

					connection.Open();
					command.ExecuteNonQuery();
					connection.Close();

					// Terms
					UpdateTerms(post);
				}
			}
		}

		/// <summary>
		/// Deletes a post from the data store.
		/// </summary>
		/// <param name="post">The post.</param>
		private void DeletePostable(IPostable post)
		{
			using (MySqlConnection connection = new MySqlConnection(BlogSettings.Instance.ConnectionString))
			{
				using (MySqlCommand command = connection.CreateCommand())
				{
					command.CommandType = CommandType.Text;
					command.CommandText = @"delete from blog_posts where post_id = ?id;";

					command.Parameters.AddWithValue("?id", post.Id);

					connection.Open();
					command.ExecuteNonQuery();
					connection.Close();
				}
			}
		}

		/// <summary>
		/// Updates the tags.
		/// </summary>
		/// <param name="post">The post.</param>
		private void UpdateTerms(IPostable post)
		{
			using (MySqlConnection connection = new MySqlConnection(BlogSettings.Instance.ConnectionString))
			{
				using (MySqlCommand command = connection.CreateCommand())
				{
					command.CommandType = CommandType.Text;
					command.CommandText = "delete t, tl from blog_term_link tl inner join blog_terms t on tl.term_id = t.term_id where post_id = ?id;";

					command.Parameters.AddWithValue("?id", post.Id);

					connection.Open();
					command.ExecuteNonQuery();

					command.CommandText = @"
insert into blog_terms (type, name, slug) values (?type, ?name, ?slug) 
	on duplicate key update name = values(name);

select @term_id := term_id from blog_terms t where t.slug = ?slug and t.type = ?type;

insert into blog_term_link (post_id, term_id) values (?id, @term_id) 
	on duplicate key update post_id = values(post_id), term_id = values(term_id);";

					foreach (Term term in post.Terms)
					{
						command.Parameters.Clear();
						command.Parameters.AddWithValue("?id", post.Id);
						command.Parameters.AddWithValue("?name", term.Name);
						command.Parameters.AddWithValue("?type", term.Type.ToString());
						command.Parameters.AddWithValue("?slug", Utils.RemoveIllegalCharacters(term.Slug));

						command.ExecuteNonQuery();
					}
					connection.Close();
				}
			}
		}

		#endregion

		#region Post

		/// <summary>
		/// Counts the posts.
		/// </summary>
		/// <returns></returns>
		public override long CountPosts()
		{
			return CountPostable(PostType.Post);
		}

		/// <summary>
		/// Retrieves a Post from the provider based on the specified id.
		/// </summary>
		/// <param name="id">The id.</param>
		/// <returns></returns>
		public override Post SelectPost(int id)
		{
			IPostable post = SelectPostable(id);

			if (post is Post)
				return post as Post;
			else
				return null;
		}

		/// <summary>
		/// Retrieves a Post from the provider based on the specified id.
		/// </summary>
		/// <param name="permalink">The permalink.</param>
		/// <returns></returns>
		public override Post SelectPost(string permalink)
		{
			IPostable post = SelectPostable(permalink);

			if (post is Post)
				return post as Post;
			else
				return null;
		}

		/// <summary>
		/// Selects the next post.
		/// </summary>
		/// <param name="id">The id.</param>
		/// <returns></returns>
		public override Post SelectNextPost(int id)
		{
			throw new NotImplementedException();
		}

		/// <summary>
		/// Selects the previous post.
		/// </summary>
		/// <param name="id">The id.</param>
		/// <returns></returns>
		public override Post SelectPreviousPost(int id)
		{
			throw new NotImplementedException();
		}

		/// <summary>
		/// Selects the posts by term.
		/// </summary>
		/// <param name="type">The type.</param>
		/// <param name="slug">The slug.</param>
		/// <param name="startIndex"></param>
		/// <param name="count"></param>
		/// <returns></returns>
		public override List<Post> SelectPostsByTerm(TermType type, string slug, int startIndex, int count)
		{
			IList<IPostable> posts = SelectPostableByTerm(PostType.Post, type, slug, startIndex, count);
			List<Post> results = new List<Post>();

			foreach (IPostable post in posts)
			{
				if (post is Post)
					results.Add(post as Post);
			}

			return results;
		}

		/// <summary>
		/// Selects the post by author.
		/// </summary>
		/// <param name="author">The author.</param>
		/// <returns></returns>
		public override List<Post> SelectPostsByAuthor(string author, int startIndex, int count)
		{
			IList<IPostable> posts = SelectPostableByAuthor(author, startIndex, count);
			List<Post> results = new List<Post>();

			foreach (IPostable post in posts)
			{
				if (post is Post)
					results.Add(post as Post);
			}

			return results;
		}


		/// <summary>
		/// Selects the post by date.
		/// </summary>
		/// <param name="year">The year.</param>
		/// <param name="month">The month.</param>
		/// <param name="day">The day.</param>
		/// <returns></returns>
		public override List<Post> SelectPostsByDate(DateTime dateFrom, DateTime dateTo, int startIndex, int count)
		{
			IList<IPostable> posts = SelectPostableByDate(dateFrom, dateTo, startIndex, count);
			List<Post> results = new List<Post>();

			foreach (IPostable post in posts)
			{
				if (post is Post)
					results.Add(post as Post);
			}

			return results;
		}

		/// <summary>
		/// Retrieves all Posts from the provider and returns them in a List.
		/// </summary>
		/// <param name="startIndex">The start index.</param>
		/// <param name="count">The count.</param>
		/// <returns></returns>
		public override List<Post> SelectPosts(int startIndex, int count)
		{
			IList<IPostable> posts = SelectPostable(PostType.Post, startIndex, count);
			List<Post> results = new List<Post>();

			foreach (IPostable post in posts)
			{
				if (post is Post)
					results.Add(post as Post);
			}

			return results;
		}

		/// <summary>
		/// Inserts a new Post into the data store specified by the provider.
		/// </summary>
		/// <param name="post">The post.</param>
		public override void InsertPost(Post post)
		{
			InsertPostable(post);
		}

		/// <summary>
		/// Updates an existing Post in the data store specified by the provider.
		/// </summary>
		/// <param name="post">The post.</param>
		public override void UpdatePost(Post post)
		{
			UpdatePostable(post);
		}

		/// <summary>
		/// Deletes a Post from the data store specified by the provider.
		/// </summary>
		/// <param name="post">The post.</param>
		public override void DeletePost(Post post)
		{
			DeletePostable(post);
		}

		#endregion

		#region Attachment

		/// <summary>
		/// Selects the attachment.
		/// </summary>
		/// <param name="id">The id.</param>
		/// <returns></returns>
		public override Attachment SelectAttachment(int id)
		{
			IPostable post = SelectPostable(id);

			if (post is Attachment)
				return post as Attachment;
			else
				return null;
		}

		/// <summary>
		/// Selects the attachment by post.
		/// </summary>
		/// <param name="startIndex">The start index.</param>
		/// <param name="count">The count.</param>
		/// <returns></returns>
		public override List<Attachment> SelectAttachments(int startIndex, int count)
		{
			IList<IPostable> posts = SelectPostable(PostType.Attachment, startIndex, count);
			List<Attachment> results = new List<Attachment>();

			foreach (IPostable post in posts)
			{
				if (post is Attachment)
					results.Add(post as Attachment);
			}

			return results;
		}

		/// <summary>
		/// Counts the attachments.
		/// </summary>
		/// <returns></returns>
		public override long CountAttachments()
		{
			return CountPostable(PostType.Attachment);
		}

		/// <summary>
		/// Inserts the attachment.
		/// </summary>
		/// <param name="attachment">The attachment.</param>
		public override void InsertAttachment(Attachment attachment)
		{
			InsertPostable(attachment);
		}

		/// <summary>
		/// Updates the attachment.
		/// </summary>
		/// <param name="attachment">The attachment.</param>
		public override void UpdateAttachment(Attachment attachment)
		{
			UpdatePostable(attachment);
		}

		/// <summary>
		/// Deletes the attachment.
		/// </summary>
		/// <param name="attachment">The attachment.</param>
		public override void DeleteAttachment(Attachment attachment)
		{
			DeletePostable(attachment);
		}

		#endregion

		#region Comments

		/// <summary>
		/// Reads the comment.
		/// </summary>
		/// <param name="reader">The reader.</param>
		/// <returns></returns>
		private Comment ReadComment(MySqlDataReader reader)
		{
			Comment comment = new Comment();

			comment.BeginInit();

			comment.Id = reader.GetInt32("comment_id");
			comment.Post = BlogService.SelectPost(reader.GetInt32("post_id"));
			comment.Type = (CommentType)Enum.Parse(typeof(CommentType), reader.GetString("type"), true);
			comment.Status = (CommentStatus)Enum.Parse(typeof(CommentStatus), reader.GetString("status"), true);
			comment.Author = reader.GetString("author_name");
			comment.Email = reader.GetString("author_email");

			Uri website;
			if (Uri.TryCreate(reader.GetString("author_website"), UriKind.RelativeOrAbsolute, out website))
				comment.Website = website;

			comment.IP = reader.GetString("author_ip");
			comment.Agent = reader.GetString("author_agent");
			comment.DateCreated = reader.GetDateTime("creation_date");

			if (!reader.IsDBNull(reader.GetOrdinal("publish_date")))
				comment.DatePublished = reader.GetDateTime("publish_date");

			comment.DateModified = reader.GetDateTime("modified_date");
			comment.Content = reader.GetString("content");

			comment.EndInit();

			return comment;
		}

		public override Comment SelectComment(int id)
		{
			using (MySqlConnection connection = new MySqlConnection(BlogSettings.Instance.ConnectionString))
			{
				using (MySqlCommand command = connection.CreateCommand())
				{
					command.CommandType = CommandType.Text;
					command.CommandText = @"select * from blog_comments where comment_id = ?id";

					command.Parameters.AddWithValue("?id", id);

					connection.Open();
					using (MySqlDataReader reader = command.ExecuteReader())
					{
						if (reader.Read())
							return ReadComment(reader);
					}
					connection.Close();
				}
			}

			return null;
		}

		public override List<Comment> SelectCommentsByPost(int postId)
		{
			List<Comment> comments = new List<Comment>();

			using (MySqlConnection connection = new MySqlConnection(BlogSettings.Instance.ConnectionString))
			{
				using (MySqlCommand command = connection.CreateCommand())
				{
					command.CommandType = CommandType.Text;
					command.CommandText = @"select * from blog_comments where post_id = ?id";

					command.Parameters.AddWithValue("?id", postId);

					connection.Open();
					using (MySqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
							comments.Add(ReadComment(reader));
					}
					connection.Close();
				}
			}

			return comments;
		}

		public override void InsertComment(Comment comment)
		{
			UpdateComment(comment);
		}

		public override void UpdateComment(Comment comment)
		{
			using (MySqlConnection connection = new MySqlConnection(BlogSettings.Instance.ConnectionString))
			{
				using (MySqlCommand command = connection.CreateCommand())
				{
					command.CommandType = CommandType.Text;
					command.CommandText = @"
insert into be_Posts (
	post_id, 
	user_name, 
	parent_id, 
	type, 
	status, 
	author_name, 
	author_email, 
	author_website, 
	author_ip, 
	author_agent,
	creation_date,
	publish_date,
	content
) values (
	?post_id, 
	?user_name, 
	?parent_id, 
	?type, 
	?status, 
	?author_name, 
	?author_email, 
	?author_website, 
	?author_ip, 
	?author_agent,
	now(),
	?publish_date,
	?content
) on duplicate key update
	comment_id = ?comment_id,
	post_id = ?post_id, 
	user_name = ?user_name, 
	parent_id = ?parent_id, 
	type = ?type, 
	status = ?status, 
	author_name = ?author_name, 
	author_email = ?author_email, 
	author_url = ?author_url, 
	author_ip = ?author_ip, 
	author_agent = ?author_agent,
	publish_date = ?publish_date,
	content = ?content";

					command.Parameters.AddWithValue("?comment_id", comment.Id);
					command.Parameters.AddWithValue("?post_id", comment.Post.Id);
					command.Parameters.AddWithValue("?user_name", null);
					command.Parameters.AddWithValue("?parent_id", null);
					command.Parameters.AddWithValue("?type", comment.Type);
					command.Parameters.AddWithValue("?status", comment.Status);
					command.Parameters.AddWithValue("?author_name", comment.Author);
					command.Parameters.AddWithValue("?author_email", comment.Email);
					command.Parameters.AddWithValue("?author_website", comment.Website);
					command.Parameters.AddWithValue("?author_ip", comment.IP);
					command.Parameters.AddWithValue("?author_agent", comment.Agent);
					command.Parameters.AddWithValue("?publish_date", comment.DatePublished);
					command.Parameters.AddWithValue("?content", comment.Content);

					command.ExecuteNonQuery();
				}
			}
		}

		public override void DeleteComment(Comment comment)
		{
			using (MySqlConnection connection = new MySqlConnection(BlogSettings.Instance.ConnectionString))
			{
				using (MySqlCommand command = connection.CreateCommand())
				{
					command.CommandType = CommandType.Text;
					command.CommandText = @"delete from blog_comments where comment_id = ?id";

					command.Parameters.AddWithValue("?id", comment.Id);

					connection.Open();
					command.ExecuteNonQuery();
					connection.Close();
				}
			}
		}

		public override void ChangeAllCommentStatusByPost(int postId, CommentStatus fromStatus, CommentStatus toStatus)
		{
			using (MySqlConnection connection = new MySqlConnection(BlogSettings.Instance.ConnectionString))
			{
				using (MySqlCommand command = connection.CreateCommand())
				{
					command.CommandType = CommandType.Text;
					command.CommandText = @"update blog_comments set status = ?toStatus where post_id = ?id and status = ?fromStatus;";

					command.Parameters.AddWithValue("?id", postId);
					command.Parameters.AddWithValue("?toStatus", toStatus);
					command.Parameters.AddWithValue("?fromStatus", fromStatus);

					connection.Open();
					command.ExecuteNonQuery();
					connection.Close();
				}
			}
		}

		#endregion

		#region Page

		public override List<Page> SelectPages()
		{
			IList<IPostable> pages = SelectPostable(PostType.Page, 0, Int32.MaxValue);
			List<Page> results = new List<Page>();

			foreach (IPostable page in pages)
			{
				if (page is Page)
					results.Add(page as Page);
			}

			return results;
		}

		public override Page SelectPage(int id)
		{
			IPostable post = SelectPostable(id);

			if (post is Page)
				return post as Page;
			else
				return null;
		}

		public override Page SelectPage(string permalink)
		{
			IPostable post = SelectPostable(permalink);

			if (post is Page)
				return post as Page;
			else
				return null;
		}

		public override void InsertPage(Page post)
		{
			InsertPostable(post);
		}

		public override void UpdatePage(Page post)
		{
			UpdatePostable(post);
		}

		public override void DeletePage(Page post)
		{
			DeletePostable(post);
		}

		#endregion

		#region Terms

		/// <summary>
		/// Reads the term.
		/// </summary>
		/// <param name="reader">The reader.</param>
		/// <returns></returns>
		private Term ReadTerm(MySqlDataReader reader)
		{
			Term term = new Term();

			term.BeginInit();

			term.Id = reader.GetInt32("term_id");
			term.Type = (TermType)Enum.Parse(typeof(TermType), reader.GetString("type"), true);
			term.Name = reader.GetString("name");
			term.Slug = reader.GetString("slug");
			term.TotalPosts = reader.GetInt32("count");

			term.EndInit();

			return term;
		}

		/// <summary>
		/// Selects the term.
		/// </summary>
		/// <param name="type">The type.</param>
		/// <param name="slug">The slug.</param>
		/// <returns></returns>
		public override Term SelectTerm(TermType type, string slug)
		{
			using (MySqlConnection connection = new MySqlConnection(BlogSettings.Instance.ConnectionString))
			{
				using (MySqlCommand command = connection.CreateCommand())
				{
					command.CommandType = CommandType.Text;
					command.CommandText = @"select t.*, count(*) as count from blog_terms t inner join blog_term_link l on t.term_id = l.term_id where type = ?type and slug = ?slug group by term_id, type, name, slug;";

					command.Parameters.AddWithValue("?type", type);
					command.Parameters.AddWithValue("?slug", slug);

					connection.Open();
					using (MySqlDataReader reader = command.ExecuteReader())
					{
						if (reader.Read())
							return ReadTerm(reader);
					}
					connection.Close();
				}
			}

			return null;
		}

		public override List<Term> SelectTerms()
		{
			List<Term> terms = new List<Term>();

			using (MySqlConnection connection = new MySqlConnection(BlogSettings.Instance.ConnectionString))
			{
				using (MySqlCommand command = connection.CreateCommand())
				{
					command.CommandType = CommandType.Text;
					command.CommandText = @"select t.*, count(*) as count from blog_terms t inner join blog_term_link l on t.term_id = l.term_id group by term_id, type, name, slug;";

					connection.Open();
					using (MySqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
							terms.Add(ReadTerm(reader));
					}
					connection.Close();
				}
			}

			return terms;
		}

		/// <summary>
		/// Selects the terms by post.
		/// </summary>
		/// <param name="postId">The post id.</param>
		/// <param name="connection">The connection.</param>
		/// <returns></returns>
		private List<Term> SelectTermsByPost(int postId, MySqlConnection connection)
		{
			List<Term> terms = new List<Term>();

			using (MySqlCommand command = connection.CreateCommand())
			{
				command.CommandType = CommandType.Text;
				command.CommandText = @"select t.*, count(*) as count from blog_terms t inner join blog_term_link l on t.term_id = l.term_id where l.post_id = ?id group by term_id, type, name, slug;";

				command.Parameters.AddWithValue("?id", postId);

				using (MySqlDataReader reader = command.ExecuteReader())
				{
					while (reader.Read())
						terms.Add(ReadTerm(reader));
				}
			}

			return terms;
		}

		/// <summary>
		/// Selects the terms by post.
		/// </summary>
		/// <param name="postId">The post id.</param>
		/// <returns></returns>
		public override List<Term> SelectTermsByPost(int postId)
		{
			using (MySqlConnection connection = new MySqlConnection(BlogSettings.Instance.ConnectionString))
			{
				try
				{
					connection.Open();
					return SelectTermsByPost(postId, connection);
				}
				finally
				{
					connection.Close();
				}
			}
		}

		#endregion

		#region Settings

		/// <summary>
		/// Loads the settings from the provider.
		/// </summary>
		/// <returns></returns>
		public override Dictionary<string, string> LoadSettings()
		{
			Dictionary<string, string> settings = new Dictionary<string, string>(StringComparer.InvariantCultureIgnoreCase);

			using (MySqlConnection connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["BlogConnectionString"].ConnectionString))
			{
				using (MySqlCommand command = connection.CreateCommand())
				{
					command.CommandType = CommandType.Text;
					command.CommandText = "select option_name, option_value from blog_options where blog_id = 0";

					connection.Open();
					using (MySqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							string name = reader.GetString(0);
							string value = reader.GetString(1);

							settings.Add(name, value);
						}
					}
					connection.Close();
				}
			}

			return settings;
		}

		/// <summary>
		/// Saves the settings to the provider.
		/// </summary>
		/// <param name="settings"></param>
		public override void SaveSettings(Dictionary<string, string> settings)
		{
			if (settings == null)
				throw new ArgumentNullException("settings");

			using (MySqlConnection connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["BlogConnectionString"].ConnectionString))
			{
				using (MySqlCommand command = connection.CreateCommand())
				{
					command.CommandText = "insert into blog_options (option_name, option_value) values (?name, ?value) on duplicate key update option_name = values(option_name), option_value = values(option_value);";

					connection.Open();
					foreach (string key in settings.Keys)
					{
						command.Parameters.Clear();
						command.Parameters.AddWithValue("?name", key);
						command.Parameters.AddWithValue("?value", settings[key]);

						command.ExecuteNonQuery();
					}
					connection.Close();
				}
			}
		}

		#endregion
	}
}
